﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Configuration;

namespace IronOne.SalesOne.DataObjects.Data
{
    public static class Db
    {
        #region ExecuteReader
        public static string GetConnectionString()
        {
            return ConfigurationManager.ConnectionStrings["DbCon"].ConnectionString;
        }

        public static SqlDataReader ExecuteReader(string sql, SqlCommand cmd, CommandType commandType)
        {
            using (SqlConnection cnn = new SqlConnection(Db.GetConnectionString()))
            {
                using (SqlCommand command = cmd == null ? new SqlCommand() : cmd)
                {
                    command.Connection = cnn;                   
                    command.Connection.Open();
                    command.CommandText = sql;
                    command.CommandType = commandType;

                    return command.ExecuteReader();
                }

            }

        }


        public static SqlDataReader ExecuteReader(string spName, SqlCommand cmd)
        {
            using (SqlConnection cnn = new SqlConnection(Db.GetConnectionString()))
            {
                using (SqlCommand command = cmd == null ? new SqlCommand() : cmd)
                {
                    command.Connection = cnn;
                    command.Connection.Open();
                    command.CommandText = spName;
                    command.CommandType = CommandType.StoredProcedure;

                    return command.ExecuteReader();
                }

            }

        }


        public static SqlDataReader ExecuteReader(string sqlText)
        {
            return ExecuteReader(sqlText, null, CommandType.Text);
        } 
        #endregion


        #region ExecuteScalar
        public static int ExecuteScalar(string sql, SqlCommand cmd, CommandType commandType)
        {
            Int32 newProdID = 0;
            using (SqlConnection cnn = new SqlConnection(Db.GetConnectionString()))
            {
                using (SqlCommand command = cmd == null ? new SqlCommand() : cmd)
                {
                    command.Connection = cnn;
                    
                    command.CommandText = sql;
                    command.CommandType = commandType;

                    try
                    {
                        command.Connection.Open();
                        newProdID = (Int32)command.ExecuteScalar();
                    }

                    catch (Exception)
                    {
                        
                        
                    }

                }
                return newProdID;
            }
        }

        public static int ExecuteScalar(string spName, SqlCommand cmd)
        {
            Int32 newProdID = 0;
            using (SqlConnection cnn = new SqlConnection(Db.GetConnectionString()))
            {
                using (SqlCommand command = cmd == null ? new SqlCommand() : cmd)
                {
                    command.Connection = cnn;
                    command.CommandText = spName;
                    command.CommandType = CommandType.StoredProcedure;

                    try
                    {
                        command.Connection.Open();
                        newProdID = (Int32)command.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        
                        throw;
                    }
                    
                }

                return newProdID;

            }
        
        
        }

        public static int ExecuteScalar(string sqlText)
        {
            return ExecuteScalar(sqlText, null, CommandType.Text);
        
        } 
        #endregion


        #region ExecuteNonQuery
        public static int ExecuteNonQuery(string sql, SqlCommand cmd, CommandType commandType, bool getID)
        {
            using (SqlConnection cnn = new SqlConnection(Db.GetConnectionString()))
            {
                using (SqlCommand command = cmd == null ? new SqlCommand() : cmd)
                {
                    command.Connection = cnn;
                    command.CommandText = sql;
                    command.CommandType = commandType;

                    cnn.Open();
                    int id = command.ExecuteNonQuery();
                    command.Parameters.Clear();

                    if (getID)
                    {
                        id = -1;
                        command.CommandText = "SELECT @@IDENTITY";
                        id = command.ExecuteNonQuery();
                    }
                    return id;
                }
            }
        }

        public static int ExecuteNonQuery(string spName, SqlCommand cmd)
        {
            return ExecuteNonQuery(spName, cmd, CommandType.StoredProcedure, false);
        }

        public static int ExecuteNonQuery(string sqlText, bool getID)
        {
            return ExecuteNonQuery(sqlText, null, CommandType.Text, getID);
        }

        public static int ExecuteNonQuery(string sqlText)
        {
            return ExecuteNonQuery(sqlText, null, CommandType.Text, false);
        } 
        #endregion

    }
}
